create or replace
trigger biu_hire 
  before insert or update on hire 
  for each row
declare
  cn_limit constant number(1) := 4;
  ln_counter number(1);
  ln_reader_id reader.reader_id%type;
  lv_lname reader.lname%type;
  lv_fname reader.lname%type;
  lv_name varchar2(200);
  
  lv_book_title book.title%type;
  ln_day_diff number;
begin
  /*select count(1) 
    into ln_counter
    from hire h 
    where :new.reader_id = h.reader_id;
  
  select reader_id, fname, lname
    into ln_reader_id, lv_fname, lv_lname
    from reader 
    where reader_id = :new.reader_id;
  
  lv_name := ln_reader_id || '-' || lv_fname || ' ' || lv_lname;
  if ln_counter > cn_limit then
     raise_application_error(-20001, 'Reader : ' || lv_name 
                      || ' cannot hire the next book due to limit of hires!');
  end if;*/
  
  if (:new.return_date is not null and  :new.hire_date > add_months(:new.return_date, -1)) then
    select title, to_date(:new.return_date)-add_months(to_date(:new.hire_date), 1) 
      into lv_book_title, ln_day_diff  
      from book b 
      where b.book_id = :new.book_id;
    
    insert into punishment(punishment_id, val, msg, reader_id, book_id)
      values (punishment_seq.nextval, ln_day_diff*.2, 'Punishment for book ' || lv_book_title, :new.reader_id, :new.book_id);
  end if;
end;
/
